﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DAL;
using DTO;

namespace BLL
{
    public class SearchBUS
    {
        public static int sotrang;
        public static SearchDAO dao = new SearchDAO();
        public static String CreateWhere(DataTable[] s)
        {

            return dao.TaoWhere(s);

        }
        public static DataTable SelectOne(int MA)
        {
            DataTable dt = new DataTable();
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT DISTINCT TENDIADIEM.MaTenDiaDiem,TENDIADIEM.TenDiaDiem, DULIEU.SoNha, DUONG.TenDuong, PHUONG.TenPhuong, QUANHUYEN.TenQuanHuyen, TINHTHANH.TenTinhThanh, DULIEU.ChuThich,DICHVU.TenDichVu,DULIEU.KinhDo,DULIEU.ViDo");
            sb.Append(" FROM TINHTHANH INNER JOIN (TENDIADIEM INNER JOIN (QUANHUYEN INNER JOIN (PHUONG INNER JOIN (DUONG INNER JOIN (DICHVU INNER JOIN DULIEU ON DICHVU.MaDichVu = DULIEU.MaDichVu) ON DUONG.MaDuong = DULIEU.MaDuong) ON PHUONG.MaPhuong = DULIEU.MaPhuong) ON QUANHUYEN.MaQuanHuyen = DULIEU.MaQuanHuyen) ON TENDIADIEM.MaTenDiaDiem = DULIEU.MaTenDiaDiem) ON TINHTHANH.MaTinhThanh = DULIEU.MaTinhThanh where TENDIADIEM.MaTenDiaDiem="+MA);
            dt = dao.SelectOne(sb.ToString());
            return dt;
        }
        public static int tongsorecord(DataTable[] ds, String madv, int page, bool flag, int sorecord)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT DISTINCT TENDIADIEM.MaTenDiaDiem,TENDIADIEM.TenDiaDiem, DULIEU.SoNha, DUONG.TenDuong, PHUONG.TenPhuong, QUANHUYEN.TenQuanHuyen, TINHTHANH.TenTinhThanh, DULIEU.ChuThich,DICHVU.TenDichVu,DULIEU.KinhDo,DULIEU.ViDo");
            sb.Append(" FROM TINHTHANH INNER JOIN (TENDIADIEM INNER JOIN (QUANHUYEN INNER JOIN (PHUONG INNER JOIN (DUONG INNER JOIN (DICHVU INNER JOIN DULIEU ON DICHVU.MaDichVu = DULIEU.MaDichVu) ON DUONG.MaDuong = DULIEU.MaDuong) ON PHUONG.MaPhuong = DULIEU.MaPhuong) ON QUANHUYEN.MaQuanHuyen = DULIEU.MaQuanHuyen) ON TENDIADIEM.MaTenDiaDiem = DULIEU.MaTenDiaDiem) ON TINHTHANH.MaTinhThanh = DULIEU.MaTinhThanh");
            String sql = null;
            if (flag)
            {
                sql = sb.ToString() + " " + CreateWhere(ds);
            }
            else
            {
                sql = sb.ToString() + " " + CreateWhere(ds) + " and DICHVU.MaDichVu=" + madv;
            }
            DataTable dk = new DataTable();
            DataTable dt = new DataTable();
            dk = dao.SelectALL(sql);
            if (dk != null)
            {
                return dk.Rows.Count;
            }
            else
            {
                return -1;
            }
        }
        public static DataTable SelectALLAV(DataTable[] ds, string madv, int page, bool flag, int sorecord)
        {
            DataTable dt = new DataTable();
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT DISTINCT TENDIADIEM.MaTenDiaDiem,TENDIADIEM.TenDiaDiem, DULIEU.SoNha, DUONG.TenDuong, PHUONG.TenPhuong, QUANHUYEN.TenQuanHuyen, TINHTHANH.TenTinhThanh, DULIEU.ChuThich,DICHVU.TenDichVu,DULIEU.KinhDo,DULIEU.ViDo");
            sb.Append(" FROM TINHTHANH INNER JOIN (TENDIADIEM INNER JOIN (QUANHUYEN INNER JOIN (PHUONG INNER JOIN (DUONG INNER JOIN (DICHVU INNER JOIN DULIEU ON DICHVU.MaDichVu = DULIEU.MaDichVu) ON DUONG.MaDuong = DULIEU.MaDuong) ON PHUONG.MaPhuong = DULIEU.MaPhuong) ON QUANHUYEN.MaQuanHuyen = DULIEU.MaQuanHuyen) ON TENDIADIEM.MaTenDiaDiem = DULIEU.MaTenDiaDiem) ON TINHTHANH.MaTinhThanh = DULIEU.MaTinhThanh");
            String sql = null;
            if (flag)
            {
                sql = sb.ToString() + " " + CreateWhere(ds);
            }
            else
            {
                sql = sb.ToString() + " " + CreateWhere(ds) + " and DICHVU.MaDichVu=" + madv;
            }
            DataTable dk = new DataTable();
            dk = dao.SelectALL(sql);
            if (dk != null)
            {
                sotrang = dk.Rows.Count;
                if (sotrang == 0) sotrang = 1;

                try
                {
                    DataColumn[] clbang = new DataColumn[] { new DataColumn("TenDiaDiem"), new DataColumn("SoNha"), new DataColumn("TenDuong"), new DataColumn("TenPhuong"), new DataColumn("TenQuanHuyen"), new DataColumn("TenTinhThanh"), new DataColumn("ChuThich"), new DataColumn("TenDichVu"), new DataColumn("MaTenDiaDiem"), new DataColumn("KinhDo"), new DataColumn("ViDo") };
                    dt.Columns.AddRange(clbang);
                    if (dk.Rows.Count == 0)
                        return null;
                    foreach (DataRow dr in Until.Paging(dk, page, sorecord))
                    {
                        DataRow dm = dt.NewRow();
                        dm["MaTenDiaDiem"] = dr["MaTenDiaDiem"];
                        dm["TenDiaDiem"] = dr["TenDiaDiem"];
                        dm["SoNha"] = dr["SoNha"];
                        dm["TenDuong"] = dr["TenDuong"];
                        dm["TenPhuong"] = dr["TenPhuong"];
                        dm["TenQuanHuyen"] = dr["TenQuanHuyen"];
                        dm["TenTinhThanh"] = dr["TenTinhThanh"];
                        dm["ChuThich"] = dr["ChuThich"];
                        dm["KinhDo"] = dr["KinhDo"];
                        dm["ViDo"] = dr["ViDo"];
                        dm["TenDichVu"] = dr["TenDichVu"];
                        dt.Rows.Add(dm);
                    }
                }
                catch { return null; }
            }
            return dt;

        }
        public static DataTable SelectALL(DataTable []ds,String madv,int page,bool flag,int sorecord)
        {
                DataTable dt = new DataTable();
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT DISTINCT TENDIADIEM.MaTenDiaDiem,TENDIADIEM.TenDiaDiem, DULIEU.SoNha, DUONG.TenDuong, PHUONG.TenPhuong, QUANHUYEN.TenQuanHuyen, TINHTHANH.TenTinhThanh, DULIEU.ChuThich,DICHVU.TenDichVu,DULIEU.KinhDo,DULIEU.ViDo");
                sb.Append(" FROM TINHTHANH INNER JOIN (TENDIADIEM INNER JOIN (QUANHUYEN INNER JOIN (PHUONG INNER JOIN (DUONG INNER JOIN (DICHVU INNER JOIN DULIEU ON DICHVU.MaDichVu = DULIEU.MaDichVu) ON DUONG.MaDuong = DULIEU.MaDuong) ON PHUONG.MaPhuong = DULIEU.MaPhuong) ON QUANHUYEN.MaQuanHuyen = DULIEU.MaQuanHuyen) ON TENDIADIEM.MaTenDiaDiem = DULIEU.MaTenDiaDiem) ON TINHTHANH.MaTinhThanh = DULIEU.MaTinhThanh");
                String sql = null;
                //kiêm tra bảng là bảng DIADIEM
                List<DataTable> dss = new List<DataTable>();
                DataTable bangluududulieu = new DataTable();
                DataColumn[] clbang = new DataColumn[] { new DataColumn("TenDiaDiem"), new DataColumn("SoNha"), new DataColumn("TenDuong"), new DataColumn("TenPhuong"), new DataColumn("TenQuanHuyen"), new DataColumn("TenTinhThanh"), new DataColumn("ChuThich"), new DataColumn("TenDichVu"), new DataColumn("MaTenDiaDiem"), new DataColumn("KinhDo"), new DataColumn("ViDo")};
                bangluududulieu.Columns.AddRange(clbang);
                int curentposition = 0;
                bool neucobangdiadiem = false;
                for (int i = 0; i < ds.Length; i++)
                { 
                    //neu la bang dia diem 
                    if (Until.GetTableName(ds[i]).ToString() == "TENDIADIEM")
                    {
                        neucobangdiadiem = true;
                        //thuc hien lay ve danh sach bang 
                        List<DataTable> dsbangphanchia = new List<DataTable>();
                        dsbangphanchia = Until.DSBANG(ds[i], 200);
                        //thuc hien tao danh sach moi 
                        //lay danh sach bang tru danh sach bang vi tri x 
                        // thuc hien tao 1 bang moi 
                        // copy data to new table 
                        //thuc hien for 
                        for (int m = 0; m < dsbangphanchia.Count; m++)
                        {
                            for (int j = 0; j < ds.Length; j++)
                            {
                                if (Until.GetTableName(ds[j]).ToString() == "TENDIADIEM")
                                {

                                    if (curentposition < dsbangphanchia.Count)
                                    {
                                        DataTable tendiadiem = new DataTable();
                                        tendiadiem = dsbangphanchia[curentposition];
                                        tendiadiem.TableName = "TENDIADIEM";
                                        dss.Add(tendiadiem);
                                        curentposition++;
                                    }
                                }
                                else
                                {
                                    dss.Add(ds[j]);
                                }

                            }
                            DataTable[] thuchien = dss.ToArray();
                            //thuc hien truy van 
                            if (flag)
                            {
                                sql = sb.ToString() + " " + CreateWhere(thuchien);
                            }
                            else
                            {
                                sql = sb.ToString() + " " + CreateWhere(thuchien) + " and (DICHVU.MaDichVu=" + madv + ")";
                            }
                            DataTable dk = new DataTable();

                            dk = dao.SelectALL(sql);
                            if (dk != null)
                            {
                                if (dk.Rows.Count > 0)
                                {
                                    //thuc hien nap du lieu vao trong bang cuoi cung
                                    Until.MegeTable(ref bangluududulieu, dk);
                                    thuchien = null;
                                    dss.Clear();
                                }

                            }
                        }
                        //sau khi thuc hien song thi tiep tuc 
                    }
                    if (neucobangdiadiem == false)
                    {
                        dss.Add(ds[i]);
                    }
                    
                                          
                }
                if (neucobangdiadiem == false)
                {
                    DataTable[] thuchien = dss.ToArray();
                    //thuc hien truy van 
                    if (flag)
                    {
                        sql = sb.ToString() + " " + CreateWhere(thuchien);
                    }
                    else
                    {
                        sql = sb.ToString() + " " + CreateWhere(thuchien) + " and (DICHVU.MaDichVu=" + madv + ")";
                    }
                    bangluududulieu = dao.SelectALL(sql);
                }

                if (bangluududulieu != null)
                {
                    sotrang = bangluududulieu.Rows.Count;
                      if (sotrang == 0) sotrang = 1;
                        try
                        {
                            DataColumn[] cl = new DataColumn[] { new DataColumn("TenDiaDiem"), new DataColumn("SoNha"), new DataColumn("TenDuong"), new DataColumn("TenPhuong"), new DataColumn("TenQuanHuyen"), new DataColumn("TenTinhThanh"), new DataColumn("ChuThich"), new DataColumn("TenDichVu"), new DataColumn("MaTenDiaDiem"), new DataColumn("KinhDo"), new DataColumn("ViDo") };
                            dt.Columns.AddRange(cl);
                            if (bangluududulieu.Rows.Count == 0)
                                return null;
                            foreach (DataRow dr in Until.Paging(bangluududulieu, page, sorecord))
                            {
                                DataRow dm = dt.NewRow();
                                dm["MaTenDiaDiem"] = dr["MaTenDiaDiem"];
                                dm["TenDiaDiem"] = dr["TenDiaDiem"];
                                dm["SoNha"] = dr["SoNha"];
                                dm["TenDuong"] = dr["TenDuong"];
                                dm["TenPhuong"] = dr["TenPhuong"];
                                dm["TenQuanHuyen"] = dr["TenQuanHuyen"];
                                dm["TenTinhThanh"] = dr["TenTinhThanh"];
                                dm["ChuThich"] = dr["ChuThich"];
                                dm["KinhDo"] = dr["KinhDo"];
                                dm["ViDo"] = dr["ViDo"];
                                dm["TenDichVu"] = dr["TenDichVu"];
                                dt.Rows.Add(dm);
                            }
                        }
                        catch { return null; }
                    }
            return dt;
        }

        public static DataTable SelectALLK(DataTable[] ds, String madv, int page, bool flag, int sorecord)
        {

            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT DISTINCT TENDIADIEM.MaTenDiaDiem,TENDIADIEM.TenDiaDiem, DULIEU.SoNha, DUONG.TenDuong, PHUONG.TenPhuong, QUANHUYEN.TenQuanHuyen, TINHTHANH.TenTinhThanh, DULIEU.ChuThich,DICHVU.TenDichVu,DULIEU.KinhDo,DULIEU.ViDo");
            sb.Append(" FROM TINHTHANH INNER JOIN (TENDIADIEM INNER JOIN (QUANHUYEN INNER JOIN (PHUONG INNER JOIN (DUONG INNER JOIN (DICHVU INNER JOIN DULIEU ON DICHVU.MaDichVu = DULIEU.MaDichVu) ON DUONG.MaDuong = DULIEU.MaDuong) ON PHUONG.MaPhuong = DULIEU.MaPhuong) ON QUANHUYEN.MaQuanHuyen = DULIEU.MaQuanHuyen) ON TENDIADIEM.MaTenDiaDiem = DULIEU.MaTenDiaDiem) ON TINHTHANH.MaTinhThanh = DULIEU.MaTinhThanh");
            String sql = null;
            if (flag)
            {
                sql = sb.ToString() + " " + CreateWhere(ds);
            }
            else
            {
                sql = sb.ToString() + " " + CreateWhere(ds) + " and DICHVU.MaDichVu=" + madv;
            }
            DataTable dk = new DataTable();
            DataTable dt = new DataTable();
            dk = dao.SelectALL(sql);
            if (dk != null)
            {
                sotrang = dk.Rows.Count / 5;

                try
                {
                    DataColumn[] cl = new DataColumn[] { new DataColumn("TenDiaDiem"), new DataColumn("SoNha"), new DataColumn("TenDuong"), new DataColumn("TenPhuong"), new DataColumn("TenQuanHuyen"), new DataColumn("TenTinhThanh"), new DataColumn("ChuThich"), new DataColumn("TenDichVu"), new DataColumn("MaTenDiaDiem"), new DataColumn("KinhDo"), new DataColumn("ViDo") };
                    dt.Columns.AddRange(cl);
                    foreach (DataRow dr in Until.Paging(dk, page, sorecord))
                    {
                        DataRow dm = dt.NewRow();
                        dm["MaTenDiaDiem"] = dr["MaTenDiaDiem"];
                        dm["TenDiaDiem"] = dr["TenDiaDiem"];
                        dm["SoNha"] = dr["SoNha"];
                        dm["TenDuong"] = dr["TenDuong"];
                        dm["TenPhuong"] = dr["TenPhuong"];
                        dm["TenQuanHuyen"] = dr["TenQuanHuyen"];
                        dm["TenTinhThanh"] = dr["TenTinhThanh"];
                        dm["ChuThich"] = dr["ChuThich"];
                        dm["KinhDo"] = dr["KinhDo"];
                        dm["ViDo"] = dr["ViDo"];
                        dm["TenDichVu"] = dr["TenDichVu"];
                        dt.Rows.Add(dm);
                    }
                }
                catch { return null; }
            }
            return dt;
        }
    }
}
